﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using MySql.Data.MySqlClient;

namespace SmokeCalcWCF
{
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerSession,
                 ConcurrencyMode = ConcurrencyMode.Multiple)]
    public class SmokeCalc : ISmokeCalc
    {
        private const string conn = "server=yowanda.com;UID=yowanda1_fauzi;PWD=xtidu;database=yowanda1_fauzi";

        public string Registration(string _user, string _office, string _officeaddress, string _pass)
        {
            try
            {
                string user = MySqlHelper.EscapeString(_user);
                string pass = MySqlHelper.EscapeString(_pass);
                string office = MySqlHelper.EscapeString(_office);
                string officeaddress = MySqlHelper.EscapeString(_officeaddress);

                using (MySqlConnection connection = new MySqlConnection(conn))
                {
                    connection.Open();

                    var sqlTran = connection.BeginTransaction();
                    var command = connection.CreateCommand();
                    command.Transaction = sqlTran;

                    try
                    {
                        command.CommandText = string.Format("insert into user values('{0}','{1}','{2}','{3}')", user, office, officeaddress, pass);
                        command.ExecuteNonQuery();
                        sqlTran.Commit();
                        return "sukses";
                    }
                    catch (Exception ex)
                    {
                        sqlTran.Rollback();
                        return ex.Message;
                    }
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public string LoginCheck(string _user, string _pass)
        {
            try
            {
                string user = MySqlHelper.EscapeString(_user);
                string pass = MySqlHelper.EscapeString(_pass);

                using (var connection = new MySqlConnection(conn))
                {
                    connection.Open();
                    string query = string.Format("select 1 from user where user_id = '{0}' and user_password = '{1}'", user, pass);
                    var command = new MySqlCommand(query, connection);
                    var reader = command.ExecuteReader();
                    return reader.HasRows ? "sukses" : "gagal";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public string ChangePassword(string _user, string _CurrentPass, string _newPass)
        {
            try
            {
                string currentPass = MySqlHelper.EscapeString(_CurrentPass);
                string newPass = MySqlHelper.EscapeString(_newPass);

                using (MySqlConnection connection = new MySqlConnection(conn))
                {
                    connection.Open();

                    var sqlTran = connection.BeginTransaction();
                    var command = connection.CreateCommand();
                    command.Transaction = sqlTran;

                    try
                    {
                        command.CommandText = string.Format("update user set password = '{0}' where id_user = '{1}'", currentPass, newPass);
                        command.ExecuteNonQuery();
                        sqlTran.Commit();
                        return "sukses";
                    }
                    catch (Exception ex)
                    {
                        sqlTran.Rollback();
                        return ex.Message;
                    }
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public List<Measurement> GetMeasurementList(string _param, string _value)
        {
            List<Measurement> list = new List<Measurement>();
            string query = "";
            string param = MySqlHelper.EscapeString(_param);
            string value = MySqlHelper.EscapeString(_value);
            DataSet ds = new DataSet();
            try
            {
                using (var connection = new MySqlConnection(conn))
                {
                    connection.Open();
                    switch (param)
                    {
                        case "all":
                            query = string.Format("select * from measurement");
                            break;
                        case "history":
                            query = string.Format("select * from measurement where chimney_id = '{0}'", value);
                            break;
                        case "user":
                            query = string.Format("select * from measurement where user_id = '{0}'", value);
                            break;
                        case "id":
                            query = string.Format("select * from measurement where measurement_id = '{0}'", value);
                            break;
                    }

                    var adapter = new MySqlDataAdapter(query, connection);
                    adapter.Fill(ds);
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        Measurement measurement = new Measurement();
                        measurement.ChimneyId = row["chimney_id"].ToString();
                        measurement.MeasurementId = row["measurement_id"].ToString();
                        measurement.ChimneyId = row["chimney_id"].ToString();
                        measurement.UserId = row["user_id"].ToString();
                        measurement.MeasurementObserver = row["measurement_observer"].ToString();
                        measurement.MeasurementDate = row["measurement_date"].ToString();
                        measurement.MeasurementTime1 = row["measurement_time1"].ToString();
                        measurement.MeasurementTime2 = row["measurement_time2"].ToString();
                        measurement.MeasurementResultRingelmann = row["measurement_resultringelmann"].ToString();
                        measurement.MeasurementUnitAddr = row["measurement_unitaddr"].ToString();
                        measurement.MeasurementEmissionAddr = row["measurement_emissionaddr"].ToString();
                        measurement.MeasurementHeight = row["measurement_height"].ToString();
                        measurement.MeasurementRelativeHeight = row["measurement_relativeheight"].ToString();
                        measurement.MeasurementDistance = row["measurement_distance"].ToString();
                        measurement.MeasurementDir1 = row["measurement_dir1"].ToString();
                        measurement.MeasurementDir2 = row["measurement_dir2"].ToString();
                        measurement.MeasurementEmissionDes1 = row["measurement_emissiondes1"].ToString();
                        measurement.MeasurementEmissionDes2 = row["measurement_emissiondes2"].ToString();
                        measurement.MeasurementEmissionColor1 = row["measurement_emissioncolor1"].ToString();
                        measurement.MeasurementEmissionColor2 = row["measurement_emissioncolor2"].ToString();
                        measurement.MeasurementPoint1 = row["measurement_point1"].ToString();
                        measurement.MeasurementPoint2 = row["measurement_point2"].ToString();
                        measurement.MeasurementBgDes1 = row["measurement_bgdes1"].ToString();
                        measurement.MeasurementBgDes2 = row["measurement_bgdes2"].ToString();
                        measurement.MeasurementBgColor1 = row["measurement_bgcolor1"].ToString();
                        measurement.MeasurementBgColor2 = row["measurement_bgcolor2"].ToString();
                        measurement.MeasurementSky1 = row["measurement_sky1"].ToString();
                        measurement.MeasurementSky2 = row["measurement_sky2"].ToString();
                        measurement.MeasurementWind1 = row["measurement_wind1"].ToString();
                        measurement.MeasurementWind2 = row["measurement_wind2"].ToString();
                        measurement.MeasurementWindDir1 = row["measurement_winddir1"].ToString();
                        measurement.MeasurementWindDir2 = row["measurement_winddir2"].ToString();
                        measurement.MeasurementTemp1 = row["measurement_temp1"].ToString();
                        measurement.MeasurementTemp2 = row["measurement_temp2"].ToString();
                        measurement.MeasurementWetTemp = row["measurement_wettemp"].ToString();
                        measurement.MeasurementRh = row["measurement_rh"].ToString();
                        measurement.MeasurementResultDetail = row["measurement_resultdetail"].ToString();
                        list.Add(measurement);
                    }
                }
                return list;
            }
            catch
            {
                return list;
            }
        }

        public string AddMeasurement(string _chimneyId, string _userId, string _observer, string _date, string _time1,
                string _time2, string _resultringelmann, string _unitaddr, string _emissionaddr, string _height,
                string _relativeheight, string _distance, string _dir1, string _dir2, string _emissiondes1, string _emissiondes2,
                string _emissioncolor1, string _emissioncolor2, string _point1, string _point2, string _bgdes1, string _bgdes2,
                string _bgcolor1, string _bgcolor2, string _sky1, string _sky2, string _wind1, string _wind2, string _winddir1,
                string _winddir2, string _temp1, string _temp2, string _wettemp, string _rh, string _resultdetail)
        {
            try
            {

                int chimneyid = string.IsNullOrEmpty(_chimneyId) ? 0 : int.Parse(_chimneyId);
                string userId = MySqlHelper.EscapeString(_userId);
                string observer = MySqlHelper.EscapeString(_observer);
                string date = MySqlHelper.EscapeString(_date);
                string time1 = MySqlHelper.EscapeString(_time1);
                string time2 = MySqlHelper.EscapeString(_time2);
                int resultringelmann = string.IsNullOrEmpty(_resultringelmann) ? 0 : int.Parse(_resultringelmann);
                string unitaddr = MySqlHelper.EscapeString(_unitaddr);
                string emissionaddr = MySqlHelper.EscapeString(_emissionaddr);
                int height = string.IsNullOrEmpty(_height) ? 0 : int.Parse(_height);
                int relativeheight = string.IsNullOrEmpty(_relativeheight) ? 0 : int.Parse(_relativeheight);
                int distance = string.IsNullOrEmpty(_distance) ? 0 : int.Parse(_distance);
                string dir1 = MySqlHelper.EscapeString(_dir1);
                string dir2 = MySqlHelper.EscapeString(_dir2);
                string emissiondes1 = MySqlHelper.EscapeString(_emissiondes1);
                string emissiondes2 = MySqlHelper.EscapeString(_emissiondes2);
                string emissioncolor1 = MySqlHelper.EscapeString(_emissioncolor1);
                string emissioncolor2 = MySqlHelper.EscapeString(_emissioncolor2);
                string point1 = MySqlHelper.EscapeString(_point1);
                string point2 = MySqlHelper.EscapeString(_point2);
                string bgdes1 = MySqlHelper.EscapeString(_bgdes1);
                string bgdes2 = MySqlHelper.EscapeString(_bgdes2);
                string bgcolor1 = MySqlHelper.EscapeString(_bgcolor1);
                string bgcolor2 = MySqlHelper.EscapeString(_bgcolor2);
                string sky1 = MySqlHelper.EscapeString(_sky1);
                string sky2 = MySqlHelper.EscapeString(_sky2);
                int wind1 = string.IsNullOrEmpty(_wind1) ? 0 : int.Parse(_wind1);
                int wind2 = string.IsNullOrEmpty(_wind2) ? 0 : int.Parse(_wind2);
                string winddir1 = MySqlHelper.EscapeString(_winddir1);
                string winddir2 = MySqlHelper.EscapeString(_winddir2);
                int temp1 = string.IsNullOrEmpty(_temp1) ? 0 : int.Parse(_temp1);
                int temp2 = string.IsNullOrEmpty(_temp2) ? 0 : int.Parse(_temp2);
                int wettemp = string.IsNullOrEmpty(_wettemp) ? 0 : int.Parse(_wettemp);
                string rh = MySqlHelper.EscapeString(_rh);
                string resultdetail = MySqlHelper.EscapeString(_resultdetail);

                using (MySqlConnection connection = new MySqlConnection(conn))
                {
                    connection.Open();

                    var sqlTran = connection.BeginTransaction();
                    var command = connection.CreateCommand();
                    command.Transaction = sqlTran;

                    try
                    {
                        command.CommandText = string.Format("insert into measurement values('','{0}','{1}','{2}','{3}','{4}','{5}'," +
                                                            "'{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}'," +
                                                            "'{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}'," +
                                                            "'{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}')",
                                                            chimneyid, userId, observer, date, time1, time2, resultringelmann,
                                                            unitaddr, emissionaddr, height, relativeheight, distance, dir1, dir2,
                                                            emissiondes1, emissiondes2, emissioncolor1, emissioncolor2, point1, point2,
                                                            bgdes1, bgdes2, bgcolor1, bgcolor2, sky1, sky2, wind1, wind2, winddir1,
                                                            winddir2, temp1, temp2, wettemp, rh, resultdetail);
                        command.ExecuteNonQuery();
                        sqlTran.Commit();
                        return "sukses";
                    }
                    catch (Exception ex)
                    {
                        sqlTran.Rollback();
                        return ex.Message;
                    }
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public string AddCompany(string _companyCategory, string _companyName, string _companyAddress, string _companyCity, string _companyProvince, string _companyPostalCode)
        {
            try
            {
                string companyCategory = MySqlHelper.EscapeString(_companyCategory);
                string companyName = MySqlHelper.EscapeString(_companyName);
                string companyAddress = MySqlHelper.EscapeString(_companyAddress);
                string companyCity = MySqlHelper.EscapeString(_companyCity);
                string companyProvince = MySqlHelper.EscapeString(_companyProvince);
                string companyPostalCode = MySqlHelper.EscapeString(_companyPostalCode);

                using (MySqlConnection connection = new MySqlConnection(conn))
                {
                    connection.Open();

                    var sqlTran = connection.BeginTransaction();
                    var command = connection.CreateCommand();
                    command.Transaction = sqlTran;

                    try
                    {
                        command.CommandText = string.Format("insert into company values('', '{0}','{1}','{2}','{3}','{4}','{5}')", companyCategory, companyName, companyAddress, companyCity, companyProvince, companyPostalCode);
                        command.ExecuteNonQuery();
                        sqlTran.Commit();
                        return "sukses";
                    }
                    catch (Exception ex)
                    {
                        sqlTran.Rollback();
                        return ex.Message;
                    }
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public List<Company> GetCompanyList(string _param, string _value)
        {
            string param = MySqlHelper.EscapeString(_param);
            string value = MySqlHelper.EscapeString(_value);
            List<Company> list = new List<Company>();
            DataSet ds = new DataSet();
            try
            {
                using (var connection = new MySqlConnection(conn))
                {
                    connection.Open();
                    string query = "";
                    switch (param)
                    {
                        case "all":
                            query = "select * from company";
                            break;
                        case "nearby":
                            query = String.Format("select * from company where company_city = '{0}'", value);
                            break;
                        case "user":
                            query = String.Format("SELECT c.company_id, c.company_category, c.company_name, " +
                                    "c.company_address, c.company_city, c.company_province, c.company_postalcode " +
                                    "FROM company c, measurement m, chimney ch " +
                                    "WHERE m.user_id = '{0}' && m.chimney_id = ch.chimney_id && ch.company_id = c.company_id " +
                                    "GROUP BY c.company_name ORDER BY c.company_id ASC", value);
                            break;
                    }
                    var adapter = new MySqlDataAdapter(query, connection);
                    adapter.Fill(ds);
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        Company company = new Company();
                        company.CompanyId = row["company_id"].ToString();
                        company.CompanyCategory = row["company_category"].ToString();
                        company.CompanyName = row["company_name"].ToString();
                        company.CompanyAddress = row["company_address"].ToString();
                        company.CompanyCity = row["company_city"].ToString();
                        company.CompanyProvince = row["company_province"].ToString();
                        company.CompanyPostalCode = row["company_postalcode"].ToString();
                        list.Add(company);
                    }
                }
                return list;
            }
            catch
            {
                return list;
            }
        }

        public string GetCompanyId(string _companyName)
        {
            string companyName = MySqlHelper.EscapeString(_companyName);
            string companyId = "";
            DataSet ds = new DataSet();
            try
            {
                using (var connection = new MySqlConnection(conn))
                {
                    string query;
                    connection.Open();
                    query = string.Format("select company_id from company where company_name = '{0}'", companyName);
                    var adapter = new MySqlDataAdapter(query, connection);
                    adapter.Fill(ds);
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        companyId = row["company_id"].ToString();
                    }
                    return companyId;
                }
            }
            catch(Exception ex)
            {
                return ex.Message;
            }
        }

        public string AddChimney(string _companyId, string _chimneyName, string _chimneyDescription, double _chimneyLatitude, double _chimneyLongitude)
        {
            try
            {
                string companyId = MySqlHelper.EscapeString(_companyId);
                string chimneyName = MySqlHelper.EscapeString(_chimneyName);
                string chimneyDescription = MySqlHelper.EscapeString(_chimneyDescription);

                using (MySqlConnection connection = new MySqlConnection(conn))
                {
                    connection.Open();

                    var sqlTran = connection.BeginTransaction();
                    var command = connection.CreateCommand();
                    command.Transaction = sqlTran;

                    try
                    {
                        command.CommandText = string.Format("insert into chimney values('', '{0}','{1}','{2}','{3}','{4}')", companyId, chimneyName, chimneyDescription, _chimneyLatitude, _chimneyLongitude);
                        command.ExecuteNonQuery();
                        sqlTran.Commit();
                        return "sukses";
                    }
                    catch (Exception ex)
                    {
                        sqlTran.Rollback();
                        return ex.Message;
                    }
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public List<Chimney> GetChimneyList(string _param, string _value1, string _value2)
        {
            List<Chimney> list = new List<Chimney>();
            string param = MySqlHelper.EscapeString(_param);
            string value1 = MySqlHelper.EscapeString(_value1);
            string value2 = MySqlHelper.EscapeString(_value2);
            DataSet ds = new DataSet();
            try
            {
                using (var connection = new MySqlConnection(conn))
                {
                    connection.Open();
                    string query = "";
                    if(param == "companyid") query = string.Format("select * from chimney where company_id = '{0}'", value1);
                    else if (param == "userid")
                        query = string.Format("SELECT ch.chimney_id, ch.company_id, ch.chimney_name, " +
                                              "ch.chimney_description, ch.chimney_latitude, ch.chimney_longitude " +
                                              "FROM measurement m, chimney ch WHERE m.user_id = '{0}' " +
                                              "&& m.chimney_id = ch.chimney_id && ch.company_id ='{1}' " +
                                              "GROUP BY ch.chimney_id " +
                                              "ORDER BY ch.company_id ASC", value1, value2);
                    var adapter = new MySqlDataAdapter(query, connection);
                    adapter.Fill(ds);
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        Chimney chimney = new Chimney();
                        chimney.ChimneyId = row["chimney_id"].ToString();
                        chimney.CompanyId = row["company_id"].ToString();
                        chimney.ChimneyName = row["chimney_name"].ToString();
                        chimney.ChimneyDescription = row["chimney_description"].ToString();
                        chimney.ChimneyLatitude = (double)row["chimney_latitude"];
                        chimney.ChimneyLongitude = (double)row["chimney_longitude"];
                        list.Add(chimney);
                    }
                }
                return list;
            }
            catch
            {
                return list;
            }
        }

        public string GetChimneyId(string _chimneyLatitude, string _chimneyLongitude)
        {
            string chimneyId = "tidak ada";
            DataSet ds = new DataSet();
            try
            {
                using (var connection = new MySqlConnection(conn))
                {
                    string query;
                    connection.Open();
                    query = string.Format("select chimney_id from chimney where chimney_latitude = '{0}' && chimney_longitude = '{1}'", _chimneyLatitude.Replace(',', '.'), _chimneyLongitude.Replace(',', '.'));
                    var adapter = new MySqlDataAdapter(query, connection);
                    adapter.Fill(ds);
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        chimneyId = row["chimney_id"].ToString();
                    }
                    return chimneyId;
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public int[] GetOpacityStatistic()
        {
            int[] result = new int[3];
            DataSet ds = new DataSet();
            try
            {
                using (var connection = new MySqlConnection(conn))
                {
                    string query;
                    connection.Open();
                    query = string.Format("SELECT MAX(measurement_resultringelmann) AS highest, MIN(measurement_resultringelmann) AS lowest, AVG(measurement_resultringelmann) AS average FROM measurement");
                    var adapter = new MySqlDataAdapter(query, connection);
                    adapter.Fill(ds);
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        result[0] = Convert.ToInt16(row["highest"]);
                        result[1] = Convert.ToInt16(row["lowest"]);
                        result[2] = Convert.ToInt16(row["average"]);
                    }
                    return result;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }
    }
}
